import paramiko,time
import os
import psutil
import json
import pymysql

# configuration param
private_key = paramiko.RSAKey.from_private_key_file('/home/hadoop/.ssh/id_rsa')
local_file = './pst.txt'
remote_file = './pst.txt'
# MySQLDB configuration
mysql_host = '192.168.1.2'
mysql_user = 'jack'
mysql_password = 'jack123456'
mysql_db = 'monitor'
charset = 'utf8'

# get all machines
def get_all_machines(mysql_host,mysql_user,mysql_password,mysql_db,charset):
    try:
        db = pymysql.connect(host=mysql_host,user=mysql_user,password=mysql_password,db=mysql_db,charset=charset)
        cursor = db.cursor()
        sql = 'select * from machines'
        m = cursor.execute(sql)
        machines = cursor.fetchall()
        db.commit()
        db.close()
        return machines
    except Exception as err:
        print(err)
        try:
            db.close()
        except Exception:
            return False
        return False

# update pst.txt
def update_pst_file(remote_host,remote_user,private_key,local_file,remote_file):
    try:
        transport = paramiko.Transport((remote_host,22))
        transport.connect(username=remote_user,pkey=private_key)
        sftp = paramiko.SFTPClient.from_transport(transport)
        sftp.put(local_file,remote_file)
        transport.close()
        return True
    except Exception as err:
        print(err)
        try:
            transport.close()
        except Exception:
            return False
        return False


# execute pst.txt
def execute_pst_file(remote_host,remote_user,remote_file):
    try:
        client = paramiko.SSHClient()
        client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        client.connect(hostname=remote_host, username=remote_user)
        cmd = "python3 {}".format(remote_file)
        ##执行命令
        stdin, stdout, stderr = client.exec_command(cmd)
        print(stdout)
        linelist = stdout.readlines()
        print(linelist)
        client.close()
        return linelist
    except Exception as err:
        print(err)
        try:
            client.close()
        except Exception:
            return False
        return False

#update machine statu if it is diable
def update_machine_statu(mysql_host,mysql_user,mysql_password,mysql_db,charset,machine):
    try:
        db = pymysql.connect(host=mysql_host,user=mysql_user,password=mysql_password,db=mysql_db,charset=charset)
        cursor = db.cursor()
        sql = 'update machines a set a.state = {} where a.id = {}'.format(machine[6],machine[0])
        print(sql)
        m = cursor.execute(sql)
        db.commit()
        db.close()
    except Exception as err:
        print(err)
        try:
            db.close()
        except Exception:
            return False
        return False

#update machine information if it extend the disk or memory
def update_machine(mysql_host,mysql_user,mysql_password,mysql_db,charset,machine,linelist):
    try:
        db = pymysql.connect(host=mysql_host,user=mysql_user,password=mysql_password,db=mysql_db,charset=charset)
        cursor = db.cursor()
        sql = 'update machines a set a.cpu = "{}", a.mem = "{}", a.disk = "{}", a.state = "{}" where a.id = {}'.format(linelist['c_total'],round(linelist['m_total']/1024/1024/1024),round(linelist['d_total']/1024/1024/1024),1,machine[0])
        print(sql)
        m = cursor.execute(sql)
        db.commit()
        db.close()
    except Exception as err:
        print(err)
        try:
            db.close()
        except Exception:
            return False
        return False

#insert the machine CPU/Mem/Disk usage rate into DB
def insert_usage(mysql_host,mysql_user,mysql_password,mysql_db,charset,machine,linelist):
    try:
        db = pymysql.connect(host=mysql_host,user=mysql_user,password=mysql_password,db=mysql_db,charset=charset)
        cursor = db.cursor()
        sql = 'INSERT INTO machines_usage(id,m_id,m_ip,m_name,cpu_usage_rate,mem_usage_rate,disk_usasge_rate,m_time) VALUES(NULL,"{}","{}","{}","{}","{}","{}",STR_TO_DATE("{}","%Y%m%d%k%i%s"))'.format(machine[0],machine[2],machine[1],linelist['c_percent'],linelist['m_percent'],linelist['d_percent'],linelist['time'])
        print(sql)
        m = cursor.execute(sql)
        db.commit()
        db.close()
    except Exception as err:
        print(err)
        try:
            db.close()
        except Exception:
            return False
        return False
        
def main():
    while True:
        machines = get_all_machines(mysql_host,mysql_user,mysql_password,mysql_db,charset)
        if machines:
            #print(machines)
            for machine in machines:
                machine = list(machine)
                print(machine)
                if update_pst_file(machine[2],machine[7],private_key,local_file,remote_file):
                    linelists = execute_pst_file(machine[2],machine[7],remote_file)
                    if linelists:
                        for linelist in linelists:
                            print(json.loads(linelist))
                            update_machine(mysql_host,mysql_user,mysql_password,mysql_db,charset,machine,json.loads(linelist))
                            insert_usage(mysql_host,mysql_user,mysql_password,mysql_db,charset,machine,json.loads(linelist))
                    else:
                        print("This Machine Execute Script Error or Empty Result.ID:{}, IP:{}".format(machine[0],machine[2]))
                else:
                    print("This Machine can't reach.ID:{}, IP: {}".format(machine[0],machine[2]))
                    machine[6] = 0
                    update_machine_statu(mysql_host,mysql_user,mysql_password,mysql_db,charset,machine)
        else:
            print("There is no machines in the list, please check the MYSQL DB connection or the machines table.")
        time.sleep(10)

if __name__ == main():
    main()
        
    
''''
linelist = stdout.readlines()
print(linelist)

data = json.loads(linelist[0])
print(data)
#result_df = pd.Series(data).to_frame('监控信息')

'''
'''
    linelist = stdout.readlines()
    print(linelist)
##读取信息
for line in stdout:
    data = json.loads(line)
    print(type(data))
    print(data["m_percent"])
##关闭连接
'''


'''
##读取信息
for line in stdout:
    data = json.loads(line)
    #print(type(data))
    print(data)
##关闭连接

client.close()
'''
'''
cmd = "free -m | awk '/^Mem/{print $2}'"
##执行命令
stdin, stdout, stderr = client.exec_command(cmd)
m_total = stdout.read().decode()
print(m_total)
cmd = 'cat /proc/cpuinfo | grep "cpu cores" | wc -l'
##执行命令
stdin, stdout, stderr = client.exec_command(cmd)
c_total = stdout.read().decode()
print(c_total)

cmd = "df | awk '/^([^F]|(F([^i]|(i[^l]))))/{print $2}'"
##执行命令
stdin, stdout, stderr = client.exec_command(cmd)
d_total = stdout.read().decode()
'''
